package cn.com.utt.unms.mapper;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.com.utt.unms.pojo.UserRecord;

@Mapper
public interface UserRecordMapper {
	
	@Update("update user_record set device_id=#{newDeviceId} where device_id=#{oriDeviceId}")
	void updateDeviceId(@Param("oriDeviceId")Long oriDeviceId,@Param("newDeviceId")Long newDeviceId);
	
	@Select({"<script>",
	    "SELECT count(id)", 
	    "FROM user_record",
	    "WHERE `status`=1", 
	    "and sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	int queryUserCount(@Param("sns")List<String> sns);
	
	@Select("select count(user_record.id) from user_record,device where user_record.device_id=device.id and user_record.device_id=#{deviceId} and device.type=1 and user_record.`status`=1")
	int queryOnlineApCount(@Param("deviceId")Long deviceId);
	
	@Select("select count(user_record.id) from user_record,device where user_record.device_id=device.id and user_record.device_id=#{deviceId} and device.type=1 and user_record.`status`=2")
	int queryOutlineApCount(@Param("deviceId")Long deviceId);
	
	@Select("select count(user_record.id) from user_record,device where user_record.device_id=device.id and user_record.device_id=#{deviceId} and device.type=0 and user_record.`status`=1 ")
	int queryOnlineRouteCount(@Param("deviceId")Long deviceId);
	
	@Select("select count(user_record.id) from user_record,device where user_record.device_id=device.id and user_record.device_id=#{deviceId} and device.type=0 and user_record.`status`=2 ")
	int queryOutlineRouteCount(@Param("deviceId")Long deviceId);
	
	@Select({"<script>",
	    "SELECT COUNT(*) AS total", 
	    "FROM user_record ",
	    "WHERE ", 
	    "<if test='sns != null and sns.size>0'>",
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "</if>",
	      "<if test='searchs != null'>",
	      "<foreach item='item' index='index' collection='searchs'",
	        "open='and' separator='and' close=''>",
	        "<choose>",
	        "<when test='item.op != \"like\"'>",
	        "${item.column}",
	        " = ",
	        "#{item.value}",
	        "</when>",
	        "<otherwise>",
	        "${item.column}",
	        " like ",
	        "'%${item.value}%'",
	        "</otherwise>",
	        "</choose>",
	      "</foreach>",
	      "</if>",
	    "</script>"})
	int countByCondition(Map<String, Object> conditions);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM user_record ",
	    "WHERE ", 
	    "<if test='sns != null and sns.size>0'>",
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "</if>",
	      "<if test='searchs != null'>",
	      "<foreach item='item' index='index' collection='searchs'",
	        "open='and' separator='and' close=''>",
	        "<choose>",
	        "<when test='item.op != \"like\"'>",
	        "${item.column}",
	        " = ",
	        "#{item.value}",
	        "</when>",
	        "<otherwise>",
	        "${item.column}",
	        " like ",
	        "'%${item.value}%'",
	        "</otherwise>",
	        "</choose>",
	      "</foreach>",
	      "</if>",
	      "<if test='orderColumn != null'>",
	      "order by ${orderColumn} ${orderDirection}",
	      "</if>",
	      " limit #{start},#{rows}",
	    "</script>"})
	List<UserRecord> queryByCondition(Map<String, Object> conditions);
	
	@Update({"<script>",
	    "update ", 
	    "user_record set status=2,end_time=now() ",
	    "WHERE ", 
	    "device_id IN", 
	      "<foreach item='item' index='index' collection='deviceIds'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and status=1 and end_time is null",
	"</script>"})
	void updateEndTime(@Param("deviceIds")List<Long> deviceIds);
	
	@Select("select band,count(id) as count from user_record where `status`=1 and sn=#{sn} group by band")
	List<Map<String, Object>> queryClientBySn(@Param("sn")String sn);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM user_record",
	    "WHERE `status`=1", 
	    "and sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      "group by mac having count(mac)>1",
	"</script>"})
	List<UserRecord> queryRepeatUser(@Param("sns") List<String> sns);
	
	@Select("select * from user_record  where mac=#{mac}  and `status`=1 order by update_time desc")
	List<UserRecord> queryUserRecordByMac(@Param("mac") String mac);
	
	@Update("update user_record set `status`=2 where `status`=1 and mac=#{mac} and (update_time<#{updateTime} or update_time is null)")
	void updateStatusByMacAndUpdateTime(@Param("mac") String mac,@Param("updateTime") String updateTime);
	
	@Update("update user_record set `status`=2 where `status`=1 and id=#{id}")
	void updateStatusById(@Param("id") long id);
	
	@Update("update user_record set `status`=2,end_time=now() where `status`=1 and id=#{id}")
	void updateStatusAndEndTimeById(@Param("id") long id);
	
	@Update("update user_record set `status`=2,end_time=now() where `status`=1 and id=#{id}")
	void updateRecordStatus(@Param("id") long id,@Param("endTime") String endTime);
	
	@Update("update user_record set `status`=3 where `status`=1 and id=#{id}")
	void updateStatuById(@Param("id") long id);
	
	@Delete({"<script>",
	    "delete from user_record where", 
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sn'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	void delete(@Param("sn") List<String> sn);
	@Select({"<script>",
	    "SELECT count(distinct hostname)", 
	    "FROM user_record",
	    "WHERE start_time>#{startTime}", 
	    "and sn IN", 
	      "<foreach item='item' index='index' collection='sn'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	int countBySn(@Param("sn") List<String> sn,@Param("startTime") String startTime);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM user_record",
	    "WHERE start_time>#{startTime}", 
	    "and sn IN", 
	      "<foreach item='item' index='index' collection='sn'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	List<UserRecord> queryBySn(@Param("sn") List<String> sn,@Param("startTime") String startTime);
	
	@Select({"<script>",
	    "SELECT *", 
	    "FROM user_record",
	    "WHERE id=#{id} and", 
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sn'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	"</script>"})
	UserRecord queryUserRecordById(@Param("sn") List<String> sn,@Param("id") Long id);
	
	@Select("select count(id) as count from user_record where mac=#{mac} and sn=#{sn} and start_time>=#{today}")
	int countTodayLoginByMac(@Param("sn") String sn,@Param("mac") String mac,@Param("today") String today);
	
	@Select("select * from user_record where mac=#{mac} and sn=#{sn} and start_time>=#{today} order by id desc limit 1")
	UserRecord queryTodayLastRecordByMac(@Param("sn") String sn,@Param("mac") String mac,@Param("today") String today);
	
	@Select("select * from user_record where sn=#{sn} and username=#{username} and  login_time >=#{today} and status=1")
	List<UserRecord> queryOnlineByUsername(@Param("sn") String sn,@Param("username") String username,@Param("today") String today);
	
	@Select("select * from user_record where sn=#{sn} and ip=#{ip} and status=1")
	List<UserRecord> queryOnlineByIP(@Param("sn") String sn,@Param("ip") String ip);
	
	@Select("select * from user_record where sn=#{sn} and ip=#{mac} and status=1")
	List<UserRecord> queryOnlineByMac(@Param("sn") String sn,@Param("mac") String mac);
	
	@Insert("insert into `user_record`(sn,auth_type,username,ip,mac,time_limit,start_time,status,token) values(#{sn},#{authType},#{username},#{ip},#{mac},#{timeLimit},#{startTime},#{status},#{token})")
	@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
	void save(UserRecord record);
	
	@Update("update user_record set status=1,end_time=null,token=#{token},username=#{username},ip=#{ip} where id=#{id}")
	void updateRecordReOnline(UserRecord record);
	
	@Update("update user_record set status=1,ip=#{ip},end_time=null where id=#{id}")
	void updateRecordReOnlineByIp(@Param("id") Long id,@Param("ip") String ip);
	
	@Select("select * from user_record where token=#{token}")
	UserRecord queryRecordByToken(@Param("token") String token);
	
	@Select("select * from user_record where mac=#{mac} order by id desc limit 1")
	UserRecord queryLastRecordByMac(@Param("mac") String mac);
}
